﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace SqlHelper
{
    public class DbHelper
    {
        #region DbConnectionString
        private static string _dbConnectionString;
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string DbConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(_dbConnectionString))
                    _dbConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DbConnectionString"].ToString();

                return _dbConnectionString;
            }
        }
        #endregion

        /// <summary>
        /// 根据数据库连接字符串,创建数据库连接对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <returns>数据库连接对象</returns>        
        public static SqlConnection CreateDbConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>返回查询结果的第一行第一列</returns>
        public static object ExecuteScalar(string sqlString, SqlConnection connection)
        {
            return ExecuteScalar(sqlString, null, connection);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="parms">命令参数数组</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>返回查询结果的第一行第一列</returns>
        public static object ExecuteScalar(string sqlString, SqlParameter[] parms, SqlConnection connection)
        {
            SqlCommand cmd = CreateDbCommand(sqlString, connection);
            if (parms != null)
                cmd.Parameters.AddRange(parms);

            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>返回查询结果影响的行数</returns>
        public static int ExecuteNonQuery(string sqlString, SqlConnection connection)
        {
            return ExecuteNonQuery(sqlString, null, connection);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="parms">命令参数数组</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>返回查询结果影响的行数</returns>
        public static int ExecuteNonQuery(string sqlString, SqlParameter[] parms, SqlConnection connection)
        {
            SqlCommand cmd = CreateDbCommand(sqlString, connection);
            if (parms != null)
                cmd.Parameters.AddRange(parms);

            return cmd.ExecuteNonQuery();
        }

        public static int ExecuteNonQueryByProc(string procName, SqlParameter[] parms, SqlConnection connection)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = connection;

            if (parms != null)
                cmd.Parameters.AddRange(parms);

            cmd.ExecuteNonQuery();

            return Convert.ToInt32(cmd.Parameters["@return"].Value);
        }

        public static int ExecuteNonQueryByProc(string procName, SqlParameter[] parms, SqlConnection connection, SqlTransaction trans)
        {
            SqlCommand cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = connection;
            if (trans != null)
                cmd.Transaction = trans;

            if (parms != null)
                cmd.Parameters.AddRange(parms);

            cmd.ExecuteNonQuery();

            return Convert.ToInt32(cmd.Parameters["@return"].Value);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sqlString, SqlConnection connection, CommandBehavior behavior)
        {
            return ExecuteReader(sqlString, null, connection, behavior);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="parms">命令参数数组</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sqlString, SqlParameter[] parms, SqlConnection connection, CommandBehavior behavior)
        {
            SqlCommand cmd = CreateDbCommand(sqlString, connection);
            if (parms != null)
                cmd.Parameters.AddRange(parms);

            return cmd.ExecuteReader(behavior);
        }

        /// <summary>
        /// 根据SQL命令获取DataTable
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>SQL命令查询结果DataTable</returns>
        public static DataTable GetTableBySQLSentence(string sqlString, SqlConnection connection)
        {
            return GetTableBySQLSentence(sqlString, null, connection);
        }

        /// <summary>
        /// 根据SQL命令获取DataTable
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="parms">命令参数数组</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>SQL命令查询结果DataTable</returns>
        public static DataTable GetTableBySQLSentence(string sqlString, SqlParameter[] parms, SqlConnection connection)
        {
            SqlDataReader reader = ExecuteReader(sqlString, parms, connection, CommandBehavior.Default);
            DataTable table = new DataTable();
            table.Load(reader);
            reader.Close();

            return table;
        }

        /// <summary>
        /// 通过存储过程获取结果集
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="parms">参数集合</param>
        /// <param name="connection">连接对象</param>
        /// <returns></returns>
        public static DataTable GetTableByProcedure(string procName, SqlParameter[] parms, SqlConnection connection)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = connection;

            if (parms != null)
                cmd.Parameters.AddRange(parms);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);

            return dt;
        }

        public static DataSet GetDataSetByProcedure(string procName, SqlParameter[] parms, SqlConnection connection)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procName;
            cmd.Connection = connection;

            if (parms != null)
                cmd.Parameters.AddRange(parms);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds;
        }


        /// <summary>
        /// 创建命令对象
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="connection">数据库连接对象</param>
        /// <returns>命令对象</returns>
        public static SqlCommand CreateDbCommand(string sqlString, SqlConnection connection)
        {
            return CreateDbCommand(sqlString, connection, null);
        }

        /// <summary>
        /// 创建命令对象
        /// </summary>
        /// <param name="sqlString">SQL命令字符串</param>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="transaction">事务对象</param>
        /// <returns>命令对象</returns>
        public static SqlCommand CreateDbCommand(string sqlString, SqlConnection connection, SqlTransaction transaction)
        {
            if (transaction == null)
                return new SqlCommand(sqlString, connection);
            else
                return new SqlCommand(sqlString, connection, transaction);
        }

        /// <summary>
        /// 创建命令参数
        /// </summary>
        /// <param name="parmName">参数名称</param>
        /// <param name="parmType">参数类型</param>
        /// <param name="parmValue">参数值</param>
        /// <returns>命令参数对象</returns>
        public static SqlParameter CreateCommandParameter(string parmName, SqlDbType parmType, object parmValue)
        {
            SqlParameter parm = new SqlParameter(parmName, parmType);
            parm.Value = parmValue;

            return parm;
        }

        /// <summary>
        /// 创建输出参数
        /// </summary>
        /// <param name="parmName"></param>
        /// <param name="parmType"></param>
        /// <returns></returns>
        public static SqlParameter CreateCommandOutParameter(string parmName, SqlDbType parmType)
        {
            SqlParameter parm = new SqlParameter(parmName, parmType);
            parm.Direction = ParameterDirection.ReturnValue;
            return parm;
        }

        public static SqlParameter CreateCommandOutputParameter(string parmName, SqlDbType parmType, int size)
        {
            SqlParameter parm = new SqlParameter(parmName, parmType, size);
            parm.Direction = ParameterDirection.Output;
            return parm;
        }
    }
}
